package db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import util.MD5;
import model.Customer;
import model.Person;

public class DBPerson implements IFPerson
{
	private Connection con;

	public DBPerson()
	{
		con = DBConnection.getInstance().getDBcon();
	}

	@Override
	public int createPerson(Person person)
	{
		int rc = -1;

		String query = "SELECT person_id FROM person WHERE person_username = ? OR person_email = ?";

		if (DBConnection.doDebug())
			DBConnection.printDebug("createPerson", query);

		try 
		{
			PreparedStatement ps = con.prepareStatement(query);

			ps.setString(1, person.getUsername());
			ps.setString(2, person.getEmail());

			ResultSet results = ps.executeQuery();

			if (!results.next())
			{
				query = "INSERT INTO person(person_username, person_password, person_phoneno, person_email, person_type, person_active) VALUES(?, ?, ?, ?, ?, ?)";

				if (DBConnection.doDebug())
					DBConnection.printDebug("createPerson", query);

				try
				{
					ps = con.prepareStatement(query, PreparedStatement.RETURN_GENERATED_KEYS);

					ps.setString(1, person.getUsername());
					ps.setString(2, MD5.md5(person.getPassword()));
					ps.setString(3, person.getPhoneNo());
					ps.setString(4, person.getEmail());
					ps.setInt(5, (person instanceof Customer ? 1 : 0));
					ps.setBoolean(6, person.isActive());

					rc = ps.executeUpdate();
					ResultSet insertedRow = ps.getGeneratedKeys();

					if ((rc > 0) && insertedRow.next())
					{
						return insertedRow.getInt(1);
					}
				}
				catch (Exception e)
				{
					if (DBConnection.doDebug())
					{
						System.out.println("[ERROR] Query exception - createPerson - INSERT: " + e);
						e.printStackTrace();
					}
				}
			}

			ps.close();
		}
		catch (Exception e)
		{
			if (DBConnection.doDebug())
			{
				System.out.println("[ERROR] Query exception - createPerson - SELECT: " + e);
				e.printStackTrace();
			}
		}

		return rc;
	}

	@Override
	public int updatePerson(Person person)
	{
		int rc = -1;

		String query = "SELECT person_id FROM person WHERE (person_username = ? OR person_email = ?) AND person_id != ?";

		if (DBConnection.doDebug())
			DBConnection.printDebug("updatePerson", query);

		try 
		{
			PreparedStatement ps = con.prepareStatement(query);

			ps.setString(1, person.getUsername());
			ps.setString(2, person.getEmail());
			ps.setInt(3, person.getPersonID());

			ResultSet results = ps.executeQuery();

			if (!results.next())
			{
				query = "UPDATE person SET person_username = ?, person_password = ?, person_phoneno = ?, person_email = ?, person_active = ? WHERE person_id = ?";

				if (DBConnection.doDebug())
					DBConnection.printDebug("updatePerson", query);

				try 
				{
					ps = con.prepareStatement(query);

					ps.setString(1, person.getUsername());
					ps.setString(2, MD5.md5(person.getPassword()));
					ps.setString(3, person.getPhoneNo());
					ps.setString(4, person.getEmail());
					ps.setBoolean(5, person.isActive());
					ps.setInt(6, person.getPersonID());

					rc = ps.executeUpdate();
				}
				catch (Exception e)
				{
					if (DBConnection.doDebug())
					{
						System.out.println("[ERROR] Query exception - updatePerson - UPDATE: " + e);
						e.printStackTrace();
					}
				}
			}

			ps.close();
		}
		catch (Exception e)
		{
			if (DBConnection.doDebug())
			{
				System.out.println("[ERROR] Query exception - createCustomer - SELECT: " + e);
				e.printStackTrace();
			}
		}

		return rc;
	}

	@Override
	public int activatePerson(int personID)
	{
		int rc = -1;

		String query = "UPDATE person SET person_active = CASE person_active WHEN 0 THEN 1 ELSE 0 END WHERE person_id = ?";

		if (DBConnection.doDebug())
			DBConnection.printDebug("activatePerson", query);

		try 
		{
			PreparedStatement ps = con.prepareStatement(query);

			ps.setInt(1, personID);

			rc = ps.executeUpdate();

			ps.close();
		}
		catch (Exception e)
		{
			if (DBConnection.doDebug())
			{
				System.out.println("[ERROR] Query exception - activatePerson - UPDATE: " + e);
				e.printStackTrace();
			}
		}

		return rc;
	}

	public void createPersonObject(Person person, ResultSet results, boolean password) throws SQLException
	{
		person.setPersonID(results.getInt("person_id"));
		person.setUsername(results.getString("person_username"));
		person.setEmail(results.getString("person_email"));
		person.setPhoneNo(results.getString("person_phoneno"));
		person.setActive(results.getBoolean("person_active"));

		if (password)
			person.setPassword(results.getString("person_password"));
	}
}